<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1" %>
<%@ page import="javax.mail.*" %>
<%@ page import="javax.mail.internet.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.lang.*" %>
<%@ page import="java.io.*" %>
<%
try {
String connectionURL = "jdbc:mysql://webproject2.cse.ust.hk/BookwarmMobile?user=BookwarmMobile&password=bookwarm";

Connection connection = null;

Class.forName("com.mysql.jdbc.Driver").newInstance();

connection = DriverManager.getConnection(connectionURL);

if(!connection.isClosed()) {
String newAccountName = null;
String newISBN = null;
String newCopyCondition = null;
String newCopyPrice = null;

newAccountName = request.getParameter("username");
newISBN = request.getParameter("isbn");
newCopyCondition = request.getParameter("copycondition");
newCopyPrice = request.getParameter("copyprice");

int count_user = 0;
Statement statement0 = null;
ResultSet rs0 = null;
statement0 = connection.createStatement();
String query0 = "SELECT count(*) AS Count FROM Users WHERE AccountName = '" + newAccountName + "';";
rs0 = statement0.executeQuery(query0);
if(rs0.next()) {
count_user = Integer.parseInt(rs0.getString("Count"));

}
statement0.close();

if(count_user == 0) {
out.print("user_not_logged_in");

}
else {
int newCopyID = 0;
Statement statement1 = null;
statement1 = connection.createStatement();
String query1 = "INSERT INTO BookCopies VALUES (DEFAULT, '" + newISBN + "', '" + newCopyCondition + "', " + newCopyPrice + ", DEFAULT, '" + newAccountName + "', DEFAULT);";
statement1.executeUpdate(query1, Statement.RETURN_GENERATED_KEYS);
ResultSet rs1 = statement1.getGeneratedKeys();
if(rs1.next()) {
newCopyID = rs1.getInt(1);

}
statement1.close();

if(newCopyID == 0) {
out.print("insert_bookcopy_failed");

}
else {
int count_record = 0;
Statement statement2 = null;
statement2 = connection.createStatement();
String query2 = "INSERT INTO Sell Values (DEFAULT, '" + newAccountName + "', " + newCopyID + ", DEFAULT, DEFAULT);";
count_record = statement2.executeUpdate(query2, Statement.NO_GENERATED_KEYS);
statement2.close();

if(count_record == 0) {
out.print("insert_sell_failed");

}
else {
out.print("insert_successful");

String newBookTitle = "";
Statement statement3 = null;
ResultSet rs3 = null;
statement3 = connection.createStatement();
String query3 = "SELECT BookTitle FROM Books WHERE ISBN = '" + newISBN + "';";
rs3 = statement3.executeQuery(query3);
if(rs3.next()) {
newBookTitle = rs3.getString("Books.BookTitle");

}
statement3.close();

Statement statement4 = null;
ResultSet rs4 = null;
statement4 = connection.createStatement();
String query4 = "SELECT AccountName FROM WishList WHERE ISBN = '" + newISBN + "' AND Status = 'Listed';";
rs4 = statement4.executeQuery(query4);
while(rs4.next()) {
String host="smtp.gmail.com", user="bookwarm.comp3111h@gmail.com", pass="bookwarmmobile";String SSL_FACTORY = "javax.net.ssl.SSLSocketFactory";
String to = rs4.getString("WishList.AccountName");
String from = "bookwarm.comp3111h@gmail.com"; 
String subject = "BookwarmMobile - New Book Copy Has Arrived";
String messageText = "<p>A new copy of the following book on your wishlist has arrived.</p><p><blockquote>Book Title: <b>" + newBookTitle + "</b></blockquote></p><p>Please check it out at your earliest convenience should you still want to get it now.</p><p>Thank you for using BookwarmMobile.</p>";
boolean sessionDebug = true;
Properties props = System.getProperties();
props.put("mail.host", host);
props.put("mail.transport.protocol.", "smtp");
props.put("mail.smtp.auth", "true");
props.put("mail.smtp.", "true");
props.put("mail.smtp.port", "465");
props.put("mail.smtp.socketFactory.fallback", "false");
props.put("mail.smtp.socketFactory.class", SSL_FACTORY);
Session mailSession = Session.getDefaultInstance(props, null);
mailSession.setDebug(sessionDebug);
Message msg = new MimeMessage(mailSession);
msg.setFrom(new InternetAddress(from));
InternetAddress[] address = {new InternetAddress(to)};
msg.setRecipients(Message.RecipientType.TO, address);
msg.setSubject(subject);
msg.setContent(messageText, "text/html"); 
Transport transport = mailSession.getTransport("smtp");
transport.connect(host, user, pass);

try {
transport.sendMessage(msg, msg.getAllRecipients());

}
catch (Exception err) {
out.println(err.getMessage());

}

transport.close();

}
statement4.close();

}

}

}

}

connection.close();

}
catch(Exception ex){
out.print(ex);

} 
%>